package com.sinba.common.system.dao;

import com.sinba.common.dao.BaseDAOImplHibernate;
import com.sinba.common.dto.PageDTO;
import com.sinba.common.system.dto.SingleGroupReportStatDTO;
import com.sinba.common.system.dto.TransactionRecordDTO;
import com.sinba.common.system.entity.TransactionRecord;
import com.sinba.common.utils.TranType;

import org.hibernate.SQLQuery;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.impl.SQLQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.util.StringUtils;

import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import javax.persistence.Query;

public class TransactionRecordDAO
    extends BaseDAOImplHibernate<TransactionRecord>
    implements ITransactionRecordDAO
{
    public PageDTO findTransactionRecordPager( TransactionRecordDTO dto, String sord, String sidx )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        if ( dto != null )
        {
            if ( StringUtils.hasText( dto.getLoginName(  ) ) )
            {
                dc.add( Restrictions.eq( "loginName",
                                         dto.getLoginName(  ) ) );
            }

            if ( StringUtils.hasText( dto.getTransactionType(  ) ) )
            {
                dc.add( Restrictions.eq( "transactionType",
                                         dto.getTransactionType(  ) ) );
            }

            if ( StringUtils.hasText( dto.getTransactionObject(  ) ) )
            {
                dc.add( Restrictions.eq( "transactionObject",
                                         dto.getTransactionObject(  ) ) );
            }

            if ( ( dto.getTransactionTypes(  ) != null ) && ( dto.getTransactionTypes(  ).length > 0 ) )
            {
                dc.add( Restrictions.in( "transactionType",
                                         dto.getTransactionTypes(  ) ) );
            }
            if ( StringUtils.hasText( dto.getDtype() ) )
            {
                dc.add( Restrictions.eq( "dtype",  dto.getDtype() ) );
            }
            //时间搜索
            if ( ( dto.getStartTime(  ) != null ) && ( dto.getEndTime(  ) == null ) )
            {
                dc.add( Restrictions.ge( "optDate",
                                         dto.getStartTime(  ) ) );
            }

            if ( ( dto.getStartTime(  ) == null ) && ( dto.getEndTime(  ) != null ) )
            {
                Calendar endTimeCl = new GregorianCalendar(  );
                endTimeCl.setTime( dto.getEndTime(  ) );
                endTimeCl.set( Calendar.DATE, endTimeCl.get( Calendar.DATE ) + 1 );
                dc.add( Restrictions.le( "optDate",
                                         endTimeCl.getTime(  ) ) );
            }

            if ( ( dto.getStartTime(  ) != null ) && ( dto.getEndTime(  ) != null ) )
            {
                Calendar endTimeCl = new GregorianCalendar(  );
                endTimeCl.setTime( dto.getEndTime(  ) );
                endTimeCl.set( Calendar.DATE, endTimeCl.get( Calendar.DATE ) + 1 );

                dc.add( Restrictions.and( Restrictions.le( 
                                                           "optDate",
                                                           endTimeCl.getTime(  ) ),
                                          Restrictions.ge( 
                                                           "optDate",
                                                           dto.getStartTime(  ) ) ) );
            }

            if ( "todaySearch".equals( dto.getSearchType(  ) ) )
            {
                Calendar timeStartCl = new GregorianCalendar(  );
                timeStartCl.setTime( new Date(  ) );
                timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
                timeStartCl.set( Calendar.MINUTE, 0 );
                timeStartCl.set( Calendar.SECOND, 0 );

                Calendar timeEndCl = new GregorianCalendar(  );
                timeEndCl.setTime( new Date(  ) );
                timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
                timeEndCl.set( Calendar.MINUTE, 59 );
                timeEndCl.set( Calendar.SECOND, 59 );

                dc.add( Restrictions.between( "optDate",
                                              timeStartCl.getTime(  ),
                                              timeEndCl.getTime(  ) ) );
            }
            
            if(dto.getRemarks()!=null && dto.getRemarks().size()>0){
            	dc.add(Restrictions.in("remark", dto.getRemarks()));
            }
            
        }

        if ( ( sord != null ) && ! sord.equals( "" ) && ( sidx != null ) && ! sidx.equals( "" ) )
        {
            if ( sord.equals( "desc" ) )
            {
                dc.addOrder( Order.desc( sidx ) );
            } else{
                dc.addOrder( Order.asc( sidx ) );
            }
        }

        return super.findPageByCriteria( dc,
                                         dto.getStart(  ),
                                         dto.getLimit(  ) );
    }
    ;

    /**
     * 判断某一账号上一天是否有交易记录
     * @param loginName
     * @return
     */
    public boolean todayIsTrans( String loginName )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
        dc.add( Restrictions.eq( "loginName", loginName ) );
        dc.add( Restrictions.eq( "transactionType", "buyIn" ) );

        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( new Date(  ) );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( new Date(  ) );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        Integer count = super.statCriteria( dc );

        if ( count > 0 )
        {
            return true;
        } else
        {
            return false;
        }
    }

    public List<TransactionRecord> findTransactionRecord( TransactionRecordDTO dto )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
        dc.add( Restrictions.in( "transactionType",
                                 new String[]
                                 {
                                     "�?��佣金", "�?��管理佣金", "财富见点佣金", "财富见点管理佣金", "成员倒钩佣金", "运营中心",
                                     "股票交易佣金 ", "静�?股票收益"
                                 } ) );
        dc.add( Restrictions.eq( "loginName",
                                 dto.getLoginName(  ) ) );
        
        return super.getHibernateTemplate(  ).findByCriteria( dc );
    }

    /**
     * 分组统计报表
     * @param dto
     * @return
     */
    public List<SingleGroupReportStatDTO> transacGroupReportStat( TransactionRecordDTO dto )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "transactionType" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( "sumOfMoney" ),
                                            "aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );
        dc.add( Restrictions.eq( "loginName",
                                 dto.getLoginName(  ) ) );
        dc.add( Restrictions.in( "transactionType",
                                 dto.getTransactionTypes(  ) ) );
        if ( StringUtils.hasText( dto.getDtype() ) )
        {
            dc.add( Restrictions.eq( "dtype",  dto.getDtype() ) );
        }
        Date date = new Date(  );

        if ( dto.getOptDate(  ) != null )
        {
            date = dto.getOptDate(  );
        }

        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( date );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( date );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );

        return list;
    }

    /**
     * 分组统计报表(与登录名来进行分�?
     * @param dto
     * @return
     */
    public List<SingleGroupReportStatDTO> transacGroupReportStatByLoginName( TransactionRecordDTO dto )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "loginName" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( "sumOfMoney" ),
                                            "aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );
        dc.add( Restrictions.in( "transactionType",
                                 dto.getTransactionTypes(  ) ) );
        if ( StringUtils.hasText( dto.getDtype() ) )
        {
            dc.add( Restrictions.eq( "dtype",  dto.getDtype() ) );
        }
        Date date = new Date(  );

        if ( dto.getOptDate(  ) != null )
        {
            date = dto.getOptDate(  );
        }

        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( date );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( date );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );

        return list;
    }

    /**
     * 统计充�?记录
     * @param dto
     * @return
     */
    public List<SingleGroupReportStatDTO> statRecharge( TransactionRecordDTO dto )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "transactionType" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( "sumOfMoney" ),
                                            "aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );
        dc.add( Restrictions.in( "transactionType",
                                 dto.getTransactionTypes(  ) ) );
        if ( StringUtils.hasText( dto.getDtype() ) )
        {
            dc.add( Restrictions.eq( "dtype",  dto.getDtype() ) );
        }
        if ( "todaySearch".equals( dto.getSearchType(  ) ) )
        {
            Date date = new Date(  );

            if ( dto.getOptDate(  ) != null )
            {
                date = dto.getOptDate(  );
            }

            Calendar timeStartCl = new GregorianCalendar(  );
            timeStartCl.setTime( date );
            timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
            timeStartCl.set( Calendar.MINUTE, 0 );
            timeStartCl.set( Calendar.SECOND, 0 );

            Calendar timeEndCl = new GregorianCalendar(  );
            timeEndCl.setTime( date );
            timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
            timeEndCl.set( Calendar.MINUTE, 59 );
            timeEndCl.set( Calendar.SECOND, 59 );

            dc.add( Restrictions.between( "optDate",
                                          timeStartCl.getTime(  ),
                                          timeEndCl.getTime(  ) ) );
        }

        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );

        return list;
    }

    //查询钱包余额
    public List<TransactionRecord> findUserBalance( String loginName, String dtype )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
        dc.add( Restrictions.eq( "loginName", loginName ) );

        if ( StringUtils.hasText( dtype ) )
        {
            dc.add( Restrictions.eq( "dtype", dtype ) );
        }

        dc.addOrder( Order.desc( "recordId" ) );

        PageDTO pageDto = super.findPageByCriteria( dc, 0, 1 );

        return pageDto.getData(  );
    }

    /**
     * 判断当天是否有交易记�?
             * @param loginName
     * @return
     */
    public boolean todayIsRecord( String loginName, String type )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        if ( loginName != null )
        {
            dc.add( Restrictions.eq( "loginName", loginName ) );
        }

        dc.add( Restrictions.eq( "transactionType", type ) );

        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( new Date(  ) );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( new Date(  ) );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        Integer count = super.statCriteria( dc );

        if ( count > 0 )
        {
            return true;
        } else
        {
            return false;
        }
    }
    public boolean todayIsRecord( String loginName, String type ,String team1)
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        if ( loginName != null )
        {
            dc.add( Restrictions.eq( "loginName", loginName ) );
        }

        dc.add( Restrictions.eq( "transactionType", type ) );
        dc.add(Restrictions.like("remark", team1, MatchMode.ANYWHERE));
        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( new Date(  ) );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( new Date(  ) );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        Integer count = super.statCriteria( dc );

        if ( count > 0 )
        {
            return true;
        } else
        {
            return false;
        }
    }
    
    /**
     * 判断当天是否有交易记�?
             * @param loginName
     * @return
     */
    public boolean todayIsRecordUseTiXian( String loginName, String type )
    {
        final DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        if ( loginName != null )
        {
            dc.add( Restrictions.eq( "loginName", loginName ) );
        }

        dc.add( Restrictions.eq( "transactionType", type ) );
        dc.add(Restrictions.ne("transactionType", TranType.JuJueTiXianTuiHui));

        Calendar timeStartCl = new GregorianCalendar(  );
        timeStartCl.setTime( new Date(  ) );
        timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
        timeStartCl.set( Calendar.MINUTE, 0 );
        timeStartCl.set( Calendar.SECOND, 0 );

        Calendar timeEndCl = new GregorianCalendar(  );
        timeEndCl.setTime( new Date(  ) );
        timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
        timeEndCl.set( Calendar.MINUTE, 59 );
        timeEndCl.set( Calendar.SECOND, 59 );

        dc.add( Restrictions.between( "optDate",
                                      timeStartCl.getTime(  ),
                                      timeEndCl.getTime(  ) ) );

        Integer count = super.statCriteria( dc );

        if ( count > 0 )
        {
            return true;
        } else
        {
            return false;
        }
    }
    
    /**
     * 统计各账户收入�?支出统计
     */
    public List<SingleGroupReportStatDTO> statAccount( TransactionRecordDTO dto )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "transactionType" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( dto.getInOrExp(  ) ),
                                            "aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );

        if ( ( dto.getTransactionTypes(  ) != null ) && ( dto.getTransactionTypes(  ).length > 0 ) )
        {
            dc.add( Restrictions.in( "transactionType",
                                     dto.getTransactionTypes(  ) ) );
        }

        if ( StringUtils.hasText( dto.getDtype(  ) ) )
        {
            dc.add( Restrictions.eq( "dtype",
                                     dto.getDtype(  ) ) );
        }

        if ( StringUtils.hasText( dto.getLoginName(  ) ) )
        {
            dc.add( Restrictions.eq( "loginName",
                                     dto.getLoginName(  ) ) );
        }

        if ( StringUtils.hasText( dto.getRemark(  ) ) )
        {
            dc.add( Restrictions.eq( "remark",
                                     dto.getRemark(  ) ) );
        }

        if ( "todaySearch".equals( dto.getSearchType(  ) ) )
        {
            Date date = new Date(  );

            if ( dto.getOptDate(  ) != null )
            {
                date = dto.getOptDate(  );
            }

            Calendar timeStartCl = new GregorianCalendar(  );
            timeStartCl.setTime( date );

            timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
            timeStartCl.set( Calendar.MINUTE, 0 );
            timeStartCl.set( Calendar.SECOND, 0 );

            Calendar timeEndCl = new GregorianCalendar(  );
            timeEndCl.setTime( date );
            timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
            timeEndCl.set( Calendar.MINUTE, 59 );
            timeEndCl.set( Calendar.SECOND, 59 );

            dc.add( Restrictions.between( "optDate",
                                          timeStartCl.getTime(  ),
                                          timeEndCl.getTime(  ) ) );
        }

        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );

        return list;
    }

    public List<TransactionRecord> findTransactionRecord( String loginName, String transactionType )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
        dc.add( Restrictions.eq( "loginName", loginName ) );
        dc.add( Restrictions.eq( "transactionType", transactionType ) );
        dc.addOrder( Order.desc( "recordId" ) );

        return super.getHibernateTemplate(  ).findByCriteria( dc );
    }

    /**
     * 统计静态、动态奖金金
     */
    public List<SingleGroupReportStatDTO> statDtype( TransactionRecordDTO dto )
    {
        DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );

        ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "dtype" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( dto.getInOrExp(  ) ),
                                            "aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );

        if ( ( dto.getTransactionTypes(  ) != null ) && ( dto.getTransactionTypes(  ).length > 0 ) )
        {
            dc.add( Restrictions.in( "transactionType",
                                     dto.getTransactionTypes(  ) ) );
        }

        if ( StringUtils.hasText( dto.getDtype(  ) ) )
        {
            dc.add( Restrictions.eq( "dtype",
                                     dto.getDtype(  ) ) );
        }

        if ( StringUtils.hasText( dto.getLoginName(  ) ) )
        {
            dc.add( Restrictions.eq( "loginName",
                                     dto.getLoginName(  ) ) );
        }

        if ( StringUtils.hasText( dto.getRemark(  ) ) )
        {
            dc.add( Restrictions.eq( "remark",
                                     dto.getRemark(  ) ) );
        }

        if ( "todaySearch".equals( dto.getSearchType(  ) ) )
        {
            Date date = new Date(  );

            if ( dto.getOptDate(  ) != null )
            {
                date = dto.getOptDate(  );
            }

            Calendar timeStartCl = new GregorianCalendar(  );
            timeStartCl.setTime( date );

            timeStartCl.set( Calendar.HOUR_OF_DAY, 0 );
            timeStartCl.set( Calendar.MINUTE, 0 );
            timeStartCl.set( Calendar.SECOND, 0 );

            Calendar timeEndCl = new GregorianCalendar(  );
            timeEndCl.setTime( date );
            timeEndCl.set( Calendar.HOUR_OF_DAY, 23 );
            timeEndCl.set( Calendar.MINUTE, 59 );
            timeEndCl.set( Calendar.SECOND, 59 );

            dc.add( Restrictions.between( "optDate",
                                          timeStartCl.getTime(  ),
                                          timeEndCl.getTime(  ) ) );
        }

        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );

        return list;
    }
    
    public Double statFengHong(String loginName){
    	DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
    	ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "loginName" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( "income" ),"aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );
    	
    	dc.add( Restrictions.eq( "loginName", loginName ) );
        dc.add( Restrictions.eq( "transactionType", TranType.RiFenHong ) );
        
        
        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );
        Double result = 0D ;
        for(SingleGroupReportStatDTO dto : list){
        	result=result+dto.getAggField();
        }
        return result;
    }
    
    public Double statOut(String loginName){
    	DetachedCriteria dc = DetachedCriteria.forClass( TransactionRecord.class );
    	ProjectionList projections = Projections.projectionList(  );
        projections.add( Projections.groupProperty( "loginName" ).as( "groupField" ) ); //要分组的字段
        projections.add( Projections.alias( Projections.sum( "income" ),"aggField" ) ); //统计
        dc.setProjection( projections );
        dc.setResultTransformer( Transformers.aliasToBean( SingleGroupReportStatDTO.class ) );
    	
    	dc.add( Restrictions.eq( "loginName", loginName ) );
        dc.add( Restrictions.in( "transactionType", new String[]{TranType.DuiPengJiang,TranType.LingDaoJiang,TranType.ZhiTuiJiang,TranType.DaiShuJiang,TranType.RiFenHong} ) );
        
        
        List<SingleGroupReportStatDTO> list = getHibernateTemplate(  ).findByCriteria( dc );
        Double dynamic = 0D ;
        Double statics = 0D ;
        for(SingleGroupReportStatDTO dto : list){
        	if("Dynamic".equals(dto.getGroupField())){
        		dynamic = dynamic+dto.getAggField();
        	}else{
        		statics=statics+dto.getAggField();
        	}
        	
        }
        return statics+dynamic/0.9;
    }
    
    /**
	 * 统计已分放静态奖金
	 * @param loginName
	 */
	public List<Object> statStatic(String loginName){
		String hql = "select sum(income) from TransactionRecord where transactionType ='RiFenHong' and loginName='"+loginName+"'";
		return super.getHibernateTemplate().find(hql);
	}
	
	 /**
	 * 统计已分放静态奖金
	 * @param loginName
	 */
	public List<Object> statStatic(String loginName,String remark){
		String hql = "select sum(income) from TransactionRecord where transactionType ='RiFenHong' and remark='"+remark+"' and loginName='"+loginName+"'";
		return super.getHibernateTemplate().find(hql);
	}
	
    
	/**
	 * 统计动态奖金余额
	 * @return List<Object[]>
	 */
	public List<Object[]> statDynamicAccountBalance(){
		String sql = "select t1.loginName,t1.balance from (select trs.loginName,trs.balance from (select tr.loginName,tr.balance from TransactionRecord as tr where tr.dtype='Dynamic' and tr.loginName not in (select ru.loginName from Register_User as ru where ru.userStatus='1' or ru.subAccount='subAccount') order by tr.recordId desc) as trs group by trs.loginName order by trs.balance desc)  as t1 where t1.balance>0";
		SQLQuery query= super.getSession().createSQLQuery(sql);
		List<Object[]> lists = query.list();
		return lists;
	}
	
	/**
	 * 统计总奖金
	 * @param loginName
	 */
	public List<Object> statTotalAward(String loginName){
		String hql = "select sum(income) from TransactionRecord where transactionType in ('RiFenHong','ZhiTuiJiang','DuiPengJiang','FuWuJiang','DaiShuJiang','BoJieJiang') and loginName='"+loginName+"'";
		return super.getHibernateTemplate().find(hql);
	}
	
	
	
}
